using System;
using System.Xml;
using System.Xml.XPath;

namespace Pegasus.Microsoft.Office.Excel
{
	/// <summary>
	/// Summary description for XmlWorksheet.
	/// </summary>
	public class XmlWorksheet
	{
		// Local Instance Values
		private string m_name = string.Empty;
		private int m_columns = 0;
		private int m_rows = 0;
		private object[,] m_cells = new object[ 0, 0 ];

		/// <summary>
		/// 
		/// </summary>
		internal XmlWorksheet( XPathNavigator worksheetNav )
		{
			m_name = worksheetNav.GetAttribute( "Name", XmlExcel.NAMESPACE_SPREADSHEET );

			// Just use the first one (there should only be one anyway)
			XPathNodeIterator tableIter = worksheetNav.SelectChildren( "Table", XmlExcel.NAMESPACE_SPREADSHEET );
			if( tableIter.MoveNext() )
			{
				XPathNavigator tableNav = tableIter.Current;
				
				string expColumns = tableNav.GetAttribute( "ExpandedColumnCount", XmlExcel.NAMESPACE_SPREADSHEET );
				if( expColumns != null && expColumns.Length > 0 )
				{
					m_columns = Convert.ToInt32( expColumns );
				}

				string expRows = tableNav.GetAttribute( "ExpandedRowCount", XmlExcel.NAMESPACE_SPREADSHEET );
				if( expRows != null && expRows.Length > 0 )
				{
					m_rows = Convert.ToInt32( expRows );
				}

				// Create the cell table
				m_cells = new object[ m_columns, m_rows ];

				LoadRowsAndCells( tableNav );

			}
		}	

		/// <summary>
		/// Get the name of the worksheet
		/// </summary>
		public string Name
		{
			get
			{
				return m_name;
			}
		}

		/// <summary>
		/// Get the number of columns in the worksheet
		/// </summary>
		public int Columns
		{
			get
			{
				return m_columns;
			}
		}

		/// <summary>
		/// Get the number of rows in the worksheet
		/// </summary>
		public int Rows
		{
			get
			{
				return m_rows;
			}
		}

		/// <summary>
		/// Get the value of the cell at the given column and row. The values are 0 based (absolute indexes).
		/// i.e. Cell [ C, 2 ] would be [ 2, 1 ]
		/// </summary>
		public object this[ int column, int row ]
		{
			get
			{
				if( column > -1 && column < m_columns && row > -1 && row < m_rows )
				{
					return m_cells[ column, row ];
				}

				return null;
			}
		}

		/// <summary>
		/// Get the value of the cell at the given column and row. These values are
		/// the Excel column (letters) and the row number (1 based)
		/// </summary>
		public object this[ string column, int row ]
		{
			get
			{
				return this[ ConvertColumnToAbsIndex( column ), row - 1 ];
			}
		}

		/// <summary>
		/// Converts the index of the column to abs.
		/// </summary>
		/// <param name="column">The column.</param>
		/// <returns></returns>
		public int ConvertColumnToAbsIndex( string column )
		{
			int absColIndex = 0;

			column = column.ToUpper();
			if( column.Length == 2 )
			{
				absColIndex += ( ( (byte) column[ 1 ] ) - ( (byte) 'A' ) ) * 26;
			}

			if( column.Length == 1 )
			{
				absColIndex += ( ( (byte) column[ 0 ] ) - ( (byte) 'A' ) );
			}

			return absColIndex;
		}

		/// <summary>
		/// Converts the abs index to column.
		/// </summary>
		/// <param name="absColumnIndex">Index of the abs column.</param>
		/// <returns></returns>
		public string ConvertAbsIndexToColumn( int absColumnIndex )
		{
			string column = string.Empty;

			int div = absColumnIndex / 26;
			int rem = absColumnIndex % 26;

			column += (char) ( ( (byte) 'A' ) + rem );
			if( div > 0 )
			{
				column = (char) ( ( (byte) 'A' ) + rem ) + column;
			}

			return column;
		}

		/// <summary>
		/// Finds the in column.
		/// </summary>
		/// <param name="column">The column.</param>
		/// <param name="value">The value.</param>
		/// <returns>
		/// The abs row index the string is in or -1 if not found.
		/// </returns>
		public int FindInColumn( string column, object value )
		{
			return FindInColumn( ConvertColumnToAbsIndex( column ), value );
		}

		/// <summary>
		/// Finds the in column.
		/// </summary>
		/// <param name="absColumnIndex">Index of the abs column.</param>
		/// <param name="value">The value.</param>
		/// <returns>
		/// The abs row index the string is in or -1 if not found.
		/// </returns>
		public int FindInColumn( int absColumnIndex, object value )
		{
			for( int y = 0; y < m_rows; y++ )
			{
				if( value.Equals( m_cells[ absColumnIndex, y ] ) )
				{
					return y;
				}
			}

			return -1;
		}

		/// <summary>
		/// Finds the in row.
		/// </summary>
		/// <param name="absRowIndex">Index of the abs row.</param>
		/// <param name="value">The value.</param>
		/// <returns>
		/// The abs column index the string is in or -1 if not found.
		/// </returns>
		public int FindInRow( int absRowIndex, object value )
		{
			for( int x = 0; x < m_columns; x++ )
			{
				if( value.Equals( m_cells[ x, absRowIndex ] ) )
				{
					return x;
				}
			}

			return -1;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="tableNav"></param>
		private void LoadRowsAndCells( XPathNavigator tableNav )
		{
			int currentColumn = 0;
			int currentRow = 0;

			// Iterate the rows
			XPathNodeIterator rowIter = tableNav.SelectChildren( "Row", XmlExcel.NAMESPACE_SPREADSHEET );
			while( rowIter.MoveNext() )
			{
				XPathNavigator rowNav = rowIter.Current;
					
				// Get the row (index) 
				string strRowIndex = rowNav.GetAttribute( "Index", XmlExcel.NAMESPACE_SPREADSHEET );
				if( strRowIndex != null && strRowIndex.Length > 0 )
				{
					currentRow = Convert.ToInt32( strRowIndex ) - 1;
				}

				// Iterate the cells in the row
				XPathNodeIterator cellIter = rowNav.SelectChildren( "Cell", XmlExcel.NAMESPACE_SPREADSHEET );
				while( cellIter.MoveNext() )
				{
					XPathNavigator cellNav = cellIter.Current;

					// Get the column (index)
					string strColIndex = cellNav.GetAttribute( "Index", XmlExcel.NAMESPACE_SPREADSHEET );
					if( strColIndex != null && strColIndex.Length > 0 )
					{
						currentColumn = Convert.ToInt32( strColIndex ) - 1;
					}

					m_cells[ currentColumn, currentRow ] = LoadData( cellNav );

					currentColumn++;
				}

				currentColumn = 0;
				currentRow++;
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="cellNav"></param>
		/// <returns></returns>
		private object LoadData( XPathNavigator cellNav )
		{
			object cellValue = null;

			// Get the data node from the cell and Just use the first 
			// one (there should only be one anyway)
			XPathNodeIterator dataIter = cellNav.SelectChildren( "Data", XmlExcel.NAMESPACE_SPREADSHEET );
			if( dataIter.MoveNext() )
			{
				XPathNavigator dataNav = dataIter.Current;
				string strType = dataNav.GetAttribute( "Type", XmlExcel.NAMESPACE_SPREADSHEET );

				// TODO: Add other types
				switch( strType )
				{
					case "String":
					default:
						cellValue = dataNav.Value;
						break;
				}
			}

			return cellValue;
		}
	}
}
